﻿USE [PlaceHolder]
GO

CREATE TABLE NAMHOC
(
	NAMHOC VARCHAR(10) PRIMARY KEY,
)

CREATE TABLE HOCKY
(
	MAHOCKY int primary key,
	TENHOCKY nvarchar(30) not null,
)
CREATE TABLE KHOI
(	
	MAKHOI varchar(3) primary key,
	KHOI nvarchar(10) not null,
)
CREATE TABLE LOP
(
	MALOP int primary key,
	MAKHOI varchar(3) FOREIGN KEY REFERENCES KHOI(MaKhoi) ON DELETE CASCADE,
	TENLOP nvarchar(50)not null,
	NAMHOC VARCHAR(10) FOREIGN KEY REFERENCES NAMHOC(NAMHOC) ON DELETE CASCADE,
	SISO int not null
)
CREATE TABLE HOSOHOCSINH
(
	MAHOCSINH int primary key not null,
	HOTEN nvarchar(50) not null,
	DIACHI nvarchar (100) not null,
	NGAYSINH smalldatetime not null,
	EMAIL varchar(50),
	GIOITINH nvarchar(3) not null,
)
CREATE TABLE XEPLOP
(
	MAHOCSINH int FOREIGN KEY REFERENCES HOSOHOCSINH(MAHOCSINH) ON DELETE CASCADE,
	MALOP int FOREIGN KEY REFERENCES LOP(MALOP) ON DELETE CASCADE,
	CONSTRAINT PK_XEPLOP PRIMARY KEY (MAHOCSINH,MALOP)
)
CREATE TABLE MONHOC
(
	MAMONHOC varchar(5) primary key,
	TENMONHOC nvarchar(30) not null,
)
CREATE TABLE LOAIDIEM
(
	MALOAIDIEM varchar(3)  primary key,
	TENLOAIDIEM NVARCHAR(30) not null,
	HESO int not null
)
CREATE TABLE DIEM
(
	MADIEM int PRIMARY KEY,
	MALOAIDIEM varchar(3) FOREIGN KEY REFERENCES LOAIDIEM(MALOAIDIEM),
	GIATRI FLOAT not null
	
)

CREATE TABLE BANGDIEM
(
	MAHOCSINH int FOREIGN KEY REFERENCES HOSOHOCSINH(MAHOCSINH) ON DELETE CASCADE,
	MAMONHOC varchar(5) FOREIGN KEY REFERENCES MONHOC(MAMONHOC) ON DELETE CASCADE,
	MAHOCKY int FOREIGN KEY REFERENCES HOCKY(MAHOCKY),
	NAMHOC VARCHAR (10) FOREIGN KEY REFERENCES NAMHOC(NAMHOC) ON DELETE CASCADE, 
	MADIEM15 int FOREIGN KEY REFERENCES DIEM(MADIEM),
	MADIEM1T int FOREIGN KEY REFERENCES DIEM(MADIEM),
	MADIEMHK int FOREIGN KEY REFERENCES DIEM(MADIEM),
	DIEMTRUNGBINH float,
	CONSTRAINT PK_BANGDIEM PRIMARY KEY (MAHOCSINH,MAHOCKY,MAMONHOC,NAMHOC)
)


CREATE TABLE THAMSO
(
	TUOITOITHIEU int,
	TUOITOIDA int,
	SISOTOIDA int,
	DIEMTOITHIEU int,
	DIEMTOIDA int,
	DIEMDATMON int,
	SOLOPTOIDAKHOI10 INT,
	SOLOPTOIDAKHOI11 INT,
	SOLOPTOIDAKHOI12 INT
)

CREATE TABLE BAOCAOMONHOC
(
	MABAOCAOMON INT PRIMARY KEY NOT NULL,
	MAMONHOC VARCHAR(5) FOREIGN KEY REFERENCES MONHOC(MAMONHOC) ON DELETE CASCADE,
	MAHOCKY INT ,
	NAMHOC VARCHAR(10) FOREIGN KEY REFERENCES NAMHOC(NAMHOC) ON DELETE CASCADE,
)

CREATE TABLE CHITIETBAOCAOMON
(
	MABAOCAOMON INT FOREIGN KEY REFERENCES BAOCAOMONHOC(MABAOCAOMON),
	MALOP INT FOREIGN KEY REFERENCES LOP(MALOP) ON DELETE CASCADE,
	SOLUONGDAT INT,
	CONSTRAINT PK_CTBAOCAOMON PRIMARY KEY (MABAOCAOMON, MALOP)
)

CREATE TABLE BAOCAOHOCKY
(
	MABAOCAOHOCKY INT PRIMARY KEY,
	MAHOCKY INT FOREIGN KEY REFERENCES HOCKY(MAHOCKY),
	NAMHOC VARCHAR(10) FOREIGN KEY REFERENCES NAMHOC(NAMHOC) ON DELETE CASCADE,
)

CREATE TABLE CHITIETBAOCAOHOCKY
(
	MABAOCAOHOCKY INT FOREIGN KEY REFERENCES BAOCAOHOCKY(MABAOCAOHOCKY),
	MALOP INT FOREIGN KEY REFERENCES LOP(MALOP) ON DELETE CASCADE,
	SOLUONGDAT INT,
	CONSTRAINT PK_CTBAOCAOHOCKY PRIMARY KEY (MABAOCAOHOCKY, MALOP)
)

--GO
--CREATE TRIGGER TRIGGER_UPDATE_BAOCAOMON ON BANGDIEM
--FOR INSERT, UPDATE
--AS
--BEGIN
--	DECLARE @MAMONHOC VARCHAR(5) = (SELECT MAMONHOC FROM INSERTED);
--	DECLARE @MAHOCKY INT = (SELECT MAHOCKY FROM INSERTED);
--	DECLARE @NAMHOC VARCHAR(10) = (SELECT NAMHOC FROM INSERTED);
--	DECLARE @MALOP INT = (SELECT LOP.MALOP FROM INSERTED, LOP, XEPLOP WHERE INSERTED.MAHOCSINH = XEPLOP.MAHOCSINH AND LOP.MALOP = XEPLOP.MALOP AND INSERTED.NAMHOC = LOP.NAMHOC);
--	DECLARE @SOLUONGDAT INT = (SELECT COUNT(DISTINCT BANGDIEM.MAHOCSINH) FROM BANGDIEM, LOP WHERE @MAHOCKY = BANGDIEM.MAHOCKY AND @MAMONHOC = BANGDIEM.MAMONHOC AND @NAMHOC = BANGDIEM.NAMHOC AND BANGDIEM.MAHOCSINH IN (SELECT MAHOCSINH FROM XEPLOP WHERE XEPLOP.MALOP = @MALOP) AND BANGDIEM.DIEMTRUNGBINH > (SELECT TOP 1 DIEMDATMON FROM THAMSO));
		
--	IF NOT EXISTS (SELECT MABAOCAOMON FROM BAOCAOMONHOC, INSERTED WHERE INSERTED.MAHOCKY = BAOCAOMONHOC.MAHOCKY AND INSERTED.MAMONHOC = BAOCAOMONHOC.MAMONHOC AND INSERTED.NAMHOC = BAOCAOMONHOC.NAMHOc )
--	BEGIN
--		IF NOT EXISTS (SELECT MABAOCAOMON FROM BAOCAOMONHOC)
--		BEGIN
--			INSERT INTO BAOCAOMONHOC VALUES(0, @MAMONHOC, @MAHOCKY, @NAMHOC)
--			INSERT INTO CHITIETBAOCAOMON VALUES(0, @MALOP, @SOLUONGDAT)
--		END
--		ELSE
--		BEGIN
--			DECLARE @NEXTMABAOCAOMON INT = (SELECT TOP 1 MABAOCAOMON FROM BAOCAOMONHOC ORDER BY MABAOCAOMON DESC) + 1;
--			INSERT INTO BAOCAOMONHOC VALUES(@NEXTMABAOCAOMON, @MAMONHOC, @MAHOCKY, @NAMHOC)
--			INSERT INTO CHITIETBAOCAOMON VALUES(@NEXTMABAOCAOMON, @MALOP, @SOLUONGDAT)
--		END
--	END
--	ELSE
--	BEGIN
--		DECLARE @MABAOCAOMON INT = (SELECT MABAOCAOMON FROM BAOCAOMONHOC,INSERTED WHERE BAOCAOMONHOC.MAHOCKY = INSERTED.MAHOCKY AND BAOCAOMONHOC.MAMONHOC = INSERTED.MAMONHOC AND BAOCAOMONHOC.NAMHOC = INSERTED.NAMHOC);
		
--		IF NOT EXISTS(SELECT * FROM CHITIETBAOCAOMON WHERE MABAOCAOMON = @MABAOCAOMON AND MALOP = @MALOP)
--		BEGIN
--			INSERT INTO CHITIETBAOCAOMON VALUES(@MABAOCAOMON, @MALOP, @SOLUONGDAT)
--		END
--		ELSE
--		BEGIN
--			UPDATE CHITIETBAOCAOMON SET SOLUONGDAT = @SOLUONGDAT WHERE MABAOCAOMON = @MABAOCAOMON AND MALOP = @MALOP
--		END
--	END
--END
GO

CREATE TRIGGER TRIGGER_DTB ON BANGDIEM
FOR INSERT, UPDATE
AS
	UPDATE BANGDIEM
	SET DIEMTRUNGBINH = ROUND((SELECT SUM(GIATRI*HESO) FROM INSERTED,DIEM, LOAIDIEM 
						WHERE (DIEM.MADIEM = INSERTED.MADIEM15 OR DIEM.MADIEM = INSERTED.MADIEM1T OR DIEM.MADIEM = INSERTED.MADIEMHK) AND DIEM.MALOAIDIEM = LOAIDIEM.MALOAIDIEM)
						/(SELECT SUM(HESO) FROM INSERTED,DIEM, LOAIDIEM WHERE (DIEM.MADIEM = INSERTED.MADIEM15 OR DIEM.MADIEM = INSERTED.MADIEM1T OR DIEM.MADIEM = INSERTED.MADIEMHK) AND DIEM.MALOAIDIEM = LOAIDIEM.MALOAIDIEM),1)
	FROM INSERTED
	WHERE INSERTED.MAHOCSINH = BANGDIEM.MAHOCSINH AND INSERTED.MAMONHOC = BANGDIEM.MAMONHOC AND INSERTED.NAMHOC = BANGDIEM.NAMHOC AND INSERTED.MAHOCKY = BANGDIEM.MAHOCKY

GO

CREATE TRIGGER TRIGGER_SISOINSERTED ON XEPLOP
FOR INSERT
AS
	UPDATE LOP
	SET SISO = SISO + 1
	FROM INSERTED
	WHERE INSERTED.MALOP = LOP.MALOP
GO

CREATE TRIGGER TRIGGER_SISOUPDATE ON XEPLOP
FOR UPDATE
AS
BEGIN
	UPDATE LOP
	SET SISO = (SELECT COUNT(XEPLOP.MAHOCSINH)
				FROM
					XEPLOP
				WHERE
					XEPLOP.MALOP IN (SELECT DISTINCT MALOP FROM INSERTED))
	FROM INSERTED
	WHERE INSERTED.MALOP = LOP.MALOP

	UPDATE LOP
	SET SISO = (SELECT COUNT(XEPLOP.MAHOCSINH)
				FROM
					XEPLOP
				WHERE
					XEPLOP.MALOP IN (SELECT DISTINCT MALOP FROM DELETED))
	FROM DELETED
	WHERE DELETED.MALOP = LOP.MALOP
END
GO

CREATE TRIGGER TRIGGER_SISODELETED ON XEPLOP
FOR DELETE
AS
	UPDATE LOP
	SET SISO = (SELECT COUNT(XEPLOP.MAHOCSINH)
				FROM
					XEPLOP
				WHERE
					XEPLOP.MALOP IN (SELECT DISTINCT MALOP FROM DELETED))
	FROM DELETED
	WHERE DELETED.MALOP = LOP.MALOP
GO

INSERT INTO KHOI VALUES ('K10',N'Khối 10')
INSERT INTO KHOI VALUES ('K11',N'Khối 11')
INSERT INTO KHOI VALUES ('K12',N'Khối 12')

INSERT INTO LOAIDIEM VALUES ('D15',N'Điểm 15 phút',1)
INSERT INTO LOAIDIEM VALUES ('D1T',N'Điểm 1 tiết',2)
INSERT INTO LOAIDIEM VALUES ('DHK',N'Điểm học kỳ',3)

INSERT INTO MONHOC VALUES('toan', N'Toán')
INSERT INTO MONHOC VALUES('ly', N'Lý')
INSERT INTO MONHOC VALUES('hoa', N'Hóa')
INSERT INTO MONHOC VALUES('sinh', N'Sinh')
INSERT INTO MONHOC VALUES('su', N'Sử')
INSERT INTO MONHOC VALUES('dia', N'Địa')
INSERT INTO MONHOC VALUES('van', N'Văn')
INSERT INTO MONHOC VALUES('dduc', N'Đạo đức')
INSERT INTO MONHOC VALUES('tduc', N'Thể dục')

INSERT INTO HOCKY VALUES (1, N'Học kỳ I')
INSERT INTO HOCKY VALUES (2, N'Học kỳ II')

INSERT INTO THAMSO VALUES (15, 20, 40, 0, 10, 5,4,3,2)